package com.thh.datamonitor.executor.dao;

import com.thh.common.exception.sql.SqlException;
import com.thh.common.utils.StringUtils;
import com.thh.datamonitor.dto.TmdTableDto;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @ClassName
 * @Desc
 * @Author tanhh
 * @Date 2022/7/8 14:47
 * @Version 1.0
 **/
@Repository
public class OracleTableDao implements TableInfoDao {
    @Override
    public List<TmdTableDto> queryList(Connection conn, String dbname, String tableName) throws SQLException {
        String sql = "select dt.table_name tableName, " +
                "       dtc.comments  tableComment, " +
                "       uo.created    createTime " +
                "  from user_tables dt, user_tab_comments dtc, user_objects uo" +
                " where dt.table_name = dtc.table_name " +
                "   and dt.table_name = uo.object_name " +
                "   and uo.object_type = 'TABLE' ";

        if (org.apache.commons.lang3.StringUtils.isNotBlank(tableName)) {
            sql += "   and dt.table_name like concat(concat('%', UPPER(?)), '%')";
        }
        sql += "order by uo.created desc";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<TmdTableDto> tableInfoListDto = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement(sql);
            if (StringUtils.isNotBlank(tableName)) {
                pstmt.setString(1, tableName);
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                TmdTableDto tmdTableDto = new TmdTableDto();
                tmdTableDto.setTableName(rs.getString("tableName"));
                tmdTableDto.setTableComment(rs.getString("tableComment"));
                tmdTableDto.setCreateTime(rs.getDate("createTime"));
                tableInfoListDto.add(tmdTableDto);
            }
        } catch (SQLException e) {
            throw new SqlException(e);
        } finally {
            close(pstmt, rs);
        }
        return tableInfoListDto;
    }

    @Override
    public TmdTableDto selectOne(Connection conn, String dbname, String tableName) throws SQLException {
        if (StringUtils.isBlank(tableName)) {
            throw new SqlException("表名tableName不能为空！");
        }
        String sql = "select dt.table_name tableName, " +
                "       dtc.comments  tableComment, " +
                "       uo.created    createTime " +
                "  from user_tables dt, user_tab_comments dtc, user_objects uo" +
                " where dt.table_name = dtc.table_name " +
                "   and dt.table_name = uo.object_name " +
                "   and uo.object_type = 'TABLE'   and dt.table_name = UPPER(?)) order by uo.created desc ";

        PreparedStatement pstmt = null;
        ResultSet rs = null;
        TmdTableDto tmdTableDto = new TmdTableDto();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, tableName);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                tmdTableDto.setTableName(rs.getString("tableName"));
                tmdTableDto.setEngine(rs.getString("engine"));
                tmdTableDto.setTableComment(rs.getString("tableComment"));
                tmdTableDto.setCreateTime(rs.getDate("createTime"));
            }
        } catch (SQLException e) {
            throw new SqlException(e);
        } finally {
            close(pstmt,rs);
        }
        return tmdTableDto;
    }

    @Override
    public String ddl(Connection conn, String dbName, String tableName) throws SQLException {
        String sql = "select dbms_metadata.get_ddl('TABLE', ?, ?) FROM DUAL";
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, tableName);
            pstmt.setString(2, dbName);
            rs = pstmt.executeQuery();
            rs.next();
            String table = rs.getString("Table");
            String createTable = rs.getString("Create Table");
            return createTable;
        } catch (Exception e) {
            throw new SqlException(e);
        } finally {
            close(pstmt,rs);
        }
    }

    @Override
    public String getPrimaryKey(Connection connection, String dbname, String tableName) throws Exception {
        String finalSql = "select  COLUMN_NAME from user_cons_columns  where constraint_name= (select  constraint_name  from user_constraints  where table_name = ? and constraint_type ='P')";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = connection.prepareStatement(finalSql);
            pstmt.setString(1, tableName);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
            throw new SqlException(e);
        } finally {
            close(pstmt,rs);
        }
        return null;
    }

    @Override
    public List<Map<String, String>> queryColumns(String tableName) {
        return null;
    }
}
